﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections;

/// <summary>


/// </summary>
public class PDB
{

    public string ConnStr; 


    public PDB()
    {
        SetDefConnStr();
    }

    public PDB(string str)
    {
        ConnStr = ConfigurationSettings.AppSettings[str];
    }

    private void SetDefConnStr()
    {
        ConnStr = ConfigurationSettings.AppSettings["POS"];
    }



   
    public void OpenConn(ref SqlConnection Conn)
    {
        try
        {
            if (ConnStr == "" || ConnStr == null)
            {
                SetDefConnStr();
            }

            Conn.ConnectionString = ConnStr;
            if (Conn.State != ConnectionState.Open)
            {
                Conn.Open();
            }
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
    }

    public void NewConn(ref SqlConnection Conn)
    {
        try
        {
            if (Conn == null)
            {
                Conn = new SqlConnection();
            }
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
    }

  
    public void CloseConn(ref SqlConnection Conn)
    {
        try
        {
            if (Conn != null)
            {
                if (Conn.State == ConnectionState.Open)
                {
                    Conn.Close();
                }
            }
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
    }

    

    public DataSet GetDataSet(ref SqlConnection Conn, string sql)
    {
        SqlDataAdapter dp;
        DataSet ds = new DataSet();
        try
        {
            NewConn(ref Conn);
            if (Conn.State != ConnectionState.Open)
            {
                OpenConn(ref Conn);
            }

            dp = new SqlDataAdapter(sql, Conn);
            dp.Fill(ds);
            return ds;

        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
    }

 
    public DataTable GetDataTable(ref SqlConnection Conn, string sql)
    {
        DataSet ds = new DataSet();
        SqlDataAdapter dp;
        try
        {
            NewConn(ref Conn);
            if (Conn.State != ConnectionState.Open)
            {
                OpenConn(ref Conn);
            }
            dp = new SqlDataAdapter(sql, Conn);
            dp.Fill(ds);
            return ds.Tables[0];
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
    }

    public DataTable GetDataTableFormat(ref SqlConnection Conn, string TableName, string col)
    {
        DataSet ds = new DataSet();
        SqlDataAdapter dp;
        string sql;
        try
        {
            NewConn(ref Conn);
            if (Conn.State != ConnectionState.Open)
            {
                OpenConn(ref Conn);
            }

            sql = "select " + col + " from " + TableName + " where 1<>1";
            dp = new SqlDataAdapter(sql, Conn);
            dp.Fill(ds, TableName);
            return ds.Tables[TableName];
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
    }


 
    public int InsertTable(ref SqlConnection Conn, DataTable dt)
    {
        int i;
        int k;
        ArrayList list = new ArrayList();

        string sql;
        try
        {
            sql = "insert into " + dt.TableName + " (";
            for (k = 0; k < dt.Columns.Count; k++)
            {
                sql += dt.Columns[k].ColumnName + ",";
            }

            sql = sql.Substring(0, sql.Length - 1);
            sql += ") Values(";

            for (i = 0; i < dt.Rows.Count; i++)
            {
                for (k = 0; k < dt.Columns.Count; k++)
                {
                    sql += GetValue(dt.Columns[k].DataType, dt.Rows[i][k].ToString()) + ",";
                }
                sql = sql.Substring(0, sql.Length - 1);
                sql += ")";
                list.Add(sql);
            }

            return ExecuteTransaction(ref Conn, list);
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {

        }
    }

    public DataTable RowAdd(ref DataTable dt, ArrayList list)
    {
        int i;
        try
        {
            DataRow dr = dt.NewRow();
            for (i = 0; i < list.Count; i++)
            {
                dr[i] = list[i].ToString();
            }
            dt.Rows.Add(dr);
            return dt;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
    }



    public string FirstCol(ref SqlConnection Conn, string sql)
    {
        object obj;
        SqlCommand cmd = new SqlCommand();
        try
        {
            NewConn(ref Conn);
            if (Conn.State != ConnectionState.Open)
            {
                OpenConn(ref Conn);
            }
            cmd.Connection = Conn;
            cmd.CommandText = sql;
            obj = cmd.ExecuteScalar();
            if (obj == null)
            {
                return "";
            }
            else
            {
                return obj.ToString();
            }

        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            cmd.Dispose();
        }

    }

  
    public int ExecuteSQl(ref SqlConnection Conn, string sql)
    {
        int i;
        SqlCommand cmd = new SqlCommand();
        try
        {
            NewConn(ref Conn);
            if (Conn.State != ConnectionState.Open)
            {
                OpenConn(ref Conn);
            }
            cmd.Connection = Conn;
            cmd.CommandText = sql;
            i = cmd.ExecuteNonQuery();
            return i;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            cmd.Dispose();
        }
    }


 
    public SqlDataReader GetReader(ref SqlConnection Conn, string sql)
    {
        SqlDataReader dr;
        SqlCommand cmd = new SqlCommand();
        try
        {
            NewConn(ref Conn);
            if (Conn.State != ConnectionState.Open)
            {
                OpenConn(ref Conn);
            }
            cmd.Connection = Conn;
            cmd.CommandText = sql;
            dr = cmd.ExecuteReader();
            return dr;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            cmd.Dispose();
        }
    }


    public int ExecuteTransaction(ref SqlConnection Conn, ArrayList list)
    {
        SqlTransaction Trans;
        SqlCommand cmd = new SqlCommand();

        NewConn(ref Conn);
        if (Conn.State != ConnectionState.Open)
        {
            OpenConn(ref Conn);
        }

        Trans = Conn.BeginTransaction();
        cmd.Transaction = Trans;
        int i = 0, iResult = 0;
        cmd.Connection = Conn;

        try
        {
            for (i = 0; i < list.Count; i++)
            {
                cmd.CommandText = list[i].ToString();
                iResult = cmd.ExecuteNonQuery();
            }
            Trans.Commit();
            return iResult;
        }
        catch (Exception ex)
        {
            Trans.Rollback();
            throw ex;
        }
        finally
        {
            Trans.Dispose();
            cmd.Dispose();
        }
    }


 
    public int ExecuteList(ref SqlConnection Conn, ArrayList list)
    {

        SqlCommand cmd = new SqlCommand();
        int i = 0, iResult = 0;

        NewConn(ref Conn);
        if (Conn.State != ConnectionState.Open)
        {
            OpenConn(ref Conn);
        }
        cmd.Connection = Conn;
        try
        {
            for (i = 0; i < list.Count; i++)
            {
                cmd.CommandText = list[i].ToString();
                iResult = cmd.ExecuteNonQuery();
            }
            return iResult;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            cmd.Dispose();
        }
    }


    public string GetValue(System.Type DataType, string Value)
    {
        if (DataType == System.Type.GetType("System.String") ||
            DataType == System.Type.GetType("System.DateTime"))
        {
            return "'" + Value + "'";
        }
        else if (DataType == System.Type.GetType("System.DBNull"))
        {
            return "null";
        }
        else if (DataType == System.Type.GetType("System.Int32") ||
            DataType == System.Type.GetType("System.Decimal") ||
            DataType == System.Type.GetType("System.Int64") ||
            DataType == System.Type.GetType("System.Double"))
        {
            return Value;
        }
        return "";
    }

    public string PageList(int size, string funClick, int countNum, int currentPage)
    {
        int pageCount = 0;
        int stepNum = 3;
        int pageRoot = 1;
        string pageStr = "";
        int pageSize = size;
        if (countNum % pageSize == 0)
        {
            pageCount = countNum / pageSize;
        }
        else
        {
            pageCount = countNum / pageSize + 1;
        }
        pageStr = "<div id='pager'>分页：" + currentPage.ToString() + "/" + pageCount.ToString() + "页";
        if (currentPage - stepNum > 1)
        {
            pageRoot = currentPage - stepNum;
        }
        int pageFoot = pageCount;
        if (currentPage + stepNum < pageCount)
        {
            pageFoot = currentPage + stepNum;
        }
        if (pageRoot == 1)
        {
            if (currentPage == 1)
            {
                pageStr += "<font color=888888 face=webdings>9</font></a>";
                pageStr += "<font color=888888 face=webdings>7</font></a> ";
            }
            else
            {
                pageStr += "<a href='#page.1' onclick='" + funClick + "(1)' title='首页'><font face=webdings>9</font></a>";
                pageStr += "<a href='#page." + Convert.ToString(currentPage - 1) + "' onclick='" + funClick + "(" + Convert.ToString(currentPage - 1) + ")' title='上页'><font face=webdings>7</font></a> ";
            }
        }
        else
        {
            pageStr += "<a href='#page.1' onclick='" + funClick + "(1)' title='首页'><font face=webdings>9</font></a>";
            pageStr += "<a href='#page." + Convert.ToString(currentPage - 1) + "' onclick='" + funClick + "(" + Convert.ToString(currentPage - 1) + ")' title='上页'><font face=webdings>7</font></a>...";
        }
        for (int i = pageRoot; i <= pageFoot; i++)
        {
            if (i == currentPage)
            {
                pageStr += "<font color='red'>[" + i.ToString() + "]</font>&nbsp;";
            }
            else
            {
                pageStr += "<a href='#page." + i.ToString() + "' onclick='" + funClick + "(" + i.ToString() + ")'>[" + i.ToString() + "]</a>&nbsp;";
            }
            if (i == pageCount)
                break;
        }
        if (pageFoot == pageCount)
        {
            if (pageCount == currentPage)
            {
                pageStr += "<font color=888888 face=webdings>8</font></a>";
                pageStr += "<font color=888888 face=webdings>:</font></a>";
            }
            else
            {
                pageStr += "<a href='#page." + Convert.ToString(currentPage + 1) + "' onclick='" + funClick + "(" + Convert.ToString(currentPage + 1) + ")' title='下页'><font face=webdings>8</font></a>";
                pageStr += "<a href='#page." + pageCount.ToString() + "' onclick='" + funClick + "(" + pageCount.ToString() + ")' title='尾页'><font face=webdings>:</font></a>";
            }
        }
        else
        {
            pageStr += "... <a href='#page." + Convert.ToString(currentPage + 1) + "' onclick='" + funClick + "(" + Convert.ToString(currentPage + 1) + ")' title='下页'><font face=webdings>8</font></a>";
            pageStr += "<a href='#page." + pageCount.ToString() + "' onclick='" + funClick + "(" + pageCount.ToString() + ")' title='尾页'><font face=webdings>:</font></a>";
        }
        pageStr += "</div>";
        return pageStr;
    }
}
